package org.dashboard.bean;

import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.swing.JOptionPane;

import org.dashboard.util.DASHConstants;
import org.dashboard.connector.DERBYConnectionFactory;


public class WSDERBYDashBoard implements IDAO {
	static Connection conn;
	
	public WSDERBYDashBoard(){
		try {
			this.conn = DERBYConnectionFactory.getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

    public void insertText(String component, String text, String namekey) throws Exception {
		PreparedStatement psInsert = null;
		Connection conn = null;
			
		if( namekey == null) throw new Exception("The value it's null");
		try
		{
			String SQL = "INSERT INTO DASHBOARD (COMPONENT, TEXT, NAMEKEY)"+
            "VALUES (?, ?, ?)";
			conn = this.conn;
			psInsert = conn.prepareStatement(SQL);
			psInsert.setString(1, component);
			psInsert.setString(2, text);
			psInsert.setString(3, namekey);
						
			int rows = psInsert.executeUpdate();

			if (rows != 1)
			{
				throw new SQLException(
					"executeUpdate return value: "
					+ rows);
			}
			

		}
			catch (SQLException ex)
			{
				JOptionPane.showMessageDialog(null, ex.getMessage());
			}
			finally
			{
				DERBYConnectionFactory.closeStatement(psInsert);
				DERBYConnectionFactory.closeJDBCConnection(conn);
				
			}
	}

	
	public void deleteText(final int id) throws Exception {
		// TODO Auto-generated method stub
		{
			PreparedStatement stmtDelete = null;
			Connection conn = null;
			
			if (id == 0)
			{
				throw new NullPointerException("Id Parameter");
			}
			
	
			try
			{
				StringBuffer sbDelete = new StringBuffer();
				
				sbDelete.append("DELETE FROM ");
				sbDelete.append("DASHBOARD");
				sbDelete.append(" WHERE ID = ?");
				
				conn = this.conn;
				stmtDelete = conn.prepareStatement(sbDelete.toString());
				
				stmtDelete.setInt(1, id);
				
				int rows = stmtDelete.executeUpdate();
				
				if (rows != 1)
				{
					throw new SQLException(
						"executeUpdate return value: "
						+ rows);
				}
				
			}
			catch (SQLException ex)
			{
				JOptionPane.showMessageDialog(null, ex.getMessage());
			}
			finally
			{
				DERBYConnectionFactory.closeStatement(stmtDelete);
				DERBYConnectionFactory.closeJDBCConnection(conn);
			}
		}

		
	}

	
	public void updateText(String component, String text, String namekey) throws Exception {  
		PreparedStatement stmtUpdate = null;
		Connection conn = null;
		// TODO Auto-generated method stub
		if (namekey == null)
		{
			throw new NullPointerException("Namekey parameter");
		}
		
		try
		{
			StringBuffer sbUpdate = new StringBuffer();
			
			sbUpdate.append("UPDATE ");
			sbUpdate.append("DASHBOARD");
			sbUpdate.append(" SET ");
			sbUpdate.append(" COMPONENT = ?, ");
			sbUpdate.append(" TEXT = ? ");
			sbUpdate.append(" WHERE ");
			sbUpdate.append(" NAMEKEY = ?");
			          
			conn = this.conn;
			stmtUpdate = conn.prepareStatement(sbUpdate.toString());
			
			
			stmtUpdate.setString(1, component);
			stmtUpdate.setString(2, text);
			stmtUpdate.setString(3, namekey);
			int rows = stmtUpdate.executeUpdate();
			
			if (rows != 1)
			{
				throw new SQLException(
					"executeUpdate return value: "
					+ rows);
			}
			
		}
		catch (SQLException ex)
		{
			System.out.println(ex.getMessage());
	   }
		finally
		{
			DERBYConnectionFactory.closeStatement(stmtUpdate);
			DERBYConnectionFactory.closeJDBCConnection(conn);
		}	
	}

	public String[] queryText(String fieldName) throws SQLException{
		String[] ret = null;
		ArrayList arrL = new ArrayList();		
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			ResultSet result = stmt
			.executeQuery("SELECT * FROM DASHBOARD");

			while (result.next()) { // process results one row at a time
				arrL.add(result.getString(fieldName));
				ret = (String[]) arrL.toArray(new String[arrL.size()]);
			}
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			JOptionPane.showMessageDialog(null, e.getMessage());
		}
		finally
		{
			DERBYConnectionFactory.closeStatement(stmt);
			DERBYConnectionFactory.closeJDBCConnection(conn);
		}	
		
		return ret;
	}
	
	public int selectQTD() throws SQLException{
		int qtd = 0;
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			ResultSet result = stmt
			.executeQuery("SELECT TEXT FROM DASHBOARD");
			while (result.next()) { // process results one row at a time
				qtd = result.getRow();
			}
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			JOptionPane.showMessageDialog(null, e.getMessage());
		}
		finally
		{
			DERBYConnectionFactory.closeStatement(stmt);
			DERBYConnectionFactory.closeJDBCConnection(conn);
		}	
		
		return qtd;
	}
	//SEARCH COORDINATE INFORMATION
	  public void insertCOORD(String coord_X, String coord_Y, String namekey) throws Exception {
			PreparedStatement psInsert = null;
			Connection conn = null;
				
			if( namekey == null) throw new Exception("The value it's null");
			try
			{
				String SQL = "INSERT INTO COORDINATE (COORD_X, COORD_Y, NAMEKEY)"+
	            "VALUES (?, ?, ?)";
				conn = this.conn;
				psInsert = conn.prepareStatement(SQL);
				psInsert.setString(1, coord_X);
				psInsert.setString(2, coord_Y);
				psInsert.setString(3, namekey);
							
				int rows = psInsert.executeUpdate();

				if (rows != 1)
				{
					throw new SQLException(
						"executeUpdate return value: "
						+ rows);
				}
				

			}
				catch (SQLException ex)
				{
					JOptionPane.showMessageDialog(null, ex.getMessage());
				}
				finally
				{
					DERBYConnectionFactory.closeStatement(psInsert);
					DERBYConnectionFactory.closeJDBCConnection(conn);
					
				}
		}

    
	public void updateCOORD(String coord_X, String coord_Y, String namekey) throws Exception {  
		PreparedStatement stmtUpdate = null;
		Connection conn = null;
		// TODO Auto-generated method stub
		if (namekey == null)
		{
			throw new NullPointerException("NAMEKEY parameter");
		}
		
		try
		{
			StringBuffer sbUpdate = new StringBuffer();
			
			sbUpdate.append("UPDATE ");
		    sbUpdate.append("COORDINATE");
			sbUpdate.append(" SET ");
			sbUpdate.append(" COORD_X = ?, ");
			sbUpdate.append(" COORD_Y = ? ");
			sbUpdate.append(" WHERE ");
			sbUpdate.append(" NAMEKEY = ?");
			          
			conn = this.conn;
			stmtUpdate = conn.prepareStatement(sbUpdate.toString());
			
			
			stmtUpdate.setString(1, coord_X);
			stmtUpdate.setString(2, coord_Y);
			stmtUpdate.setString(3, namekey);
			int rows = stmtUpdate.executeUpdate();
			
			if (rows != 1)
			{
				throw new SQLException(
					"executeUpdate return value: "
					+ rows);
			}
			
		}
		catch (SQLException ex)
		{
			System.out.println(ex.getMessage());
	   }
		finally
		{
			DERBYConnectionFactory.closeStatement(stmtUpdate);
			DERBYConnectionFactory.closeJDBCConnection(conn);
		}	
	}
	
	public void deleteCOORD(final int id) throws Exception {
		// TODO Auto-generated method stub
		{
			PreparedStatement stmtDelete = null;
			Connection conn = null;
			
			if (id == 0)
			{
				throw new NullPointerException("Id Parameter");
			}
			
	
			try
			{
				StringBuffer sbDelete = new StringBuffer();
				
				sbDelete.append("DELETE FROM ");
				sbDelete.append("COORDINATE");
				sbDelete.append(" WHERE ID = ?");
				
				conn = this.conn;
				stmtDelete = conn.prepareStatement(sbDelete.toString());
				
				stmtDelete.setInt(1, id);
				
				int rows = stmtDelete.executeUpdate();
				
				if (rows != 1)
				{
					throw new SQLException(
						"executeUpdate return value: "
						+ rows);
				}
				
			}
			catch (SQLException ex)
			{
				JOptionPane.showMessageDialog(null, ex.getMessage());
			}
			finally
			{
				DERBYConnectionFactory.closeStatement(stmtDelete);
				DERBYConnectionFactory.closeJDBCConnection(conn);
			}
		}
		
	}
	
	public String[] queryCOORD(String fieldName) throws SQLException{
		String[] ret = null;
		ArrayList arrL = new ArrayList();		
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			ResultSet result = stmt
			.executeQuery("SELECT * FROM COORDINATE");

			while (result.next()) { // process results one row at a time
				arrL.add(result.getString(fieldName));
				ret = (String[]) arrL.toArray(new String[arrL.size()]);
			}
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			JOptionPane.showMessageDialog(null, e.getMessage());
		}
		finally
		{
			DERBYConnectionFactory.closeStatement(stmt);
			DERBYConnectionFactory.closeJDBCConnection(conn);
		}	
		
		return ret;
	}
	


	
}
